﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class Vendor
    {
        private const string SQL_SELECT_VENDOR = "Select V.VendorNo, V.VendorName, V.ShortName, V.Contact, V.ContactTitle, V.Tel, V.Mobile, V.Fax, V.Address, V.ZipCode, V.BankAccount, V.Bank, V.Credit, V.CreditPeriod, V.TaxAccount, V.Region, V.VendorType, S.SubjectName as TypeName, V.Remark, V.RecordId from Vendor V left join SubjectMessage S on V.VendorType = S.SubjectId";
        private const string SQL_WHERE = " where 1 > 2";
        private const string SQL_WHERE_RECORDID = " where V.RecordId = @RecordId";

        public DataTable GetVendorList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_VENDOR, null);
        }

        //创建数据表结构
        public DataTable VendorTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_VENDOR + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的供应商资料
        public DataRow GetVendorByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_VENDOR + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public VendorInfo GetVendorInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            VendorInfo vendorInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_VENDOR + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    vendorInfo = new VendorInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7),
                                                                dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDecimal(12), dr.GetInt32(13), dr.GetString(14), dr.GetString(15),
                                                                dr.GetInt32(16), dr.GetString(18), dr.GetInt32(19));
                }
            }
            return vendorInfo;
        }

        //检测供应商编号的唯一性
        public bool IsUniqueVendorNo(string vendorNo, int recordId)
        {
            string sqlSelect = "Select count(VendorNo) from Vendor where ltrim(rtrim(VendorNo)) = @VendorNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@VendorNo", SqlDbType.VarChar, 8),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = vendorNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //检测供应商名称的唯一性
        public bool IsUniqueVendorName(string vendorName, int recordId)
        {
            string sqlSelect = "Select count(VendorName) from Vendor where ltrim(rtrim(VendorName)) = @VendorName and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@VendorName", SqlDbType.VarChar, 200),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = vendorName.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //检测供应商简称的唯一性
        public bool IsUniqueShortName(string shortName, int recordId)
        {
            string sqlSelect = "Select count(ShortName) from Vendor where ltrim(rtrim(ShortName)) = @ShortName and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@ShortName", SqlDbType.VarChar, 60),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = shortName.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        public void WriteToDatabase(string action, VendorInfo vendor, out int recordId)
        {
            string sql = "Exec Usp_ModifyVendor @p_Action, @p_VendorNo, @p_VendorName, @p_ShortName, @p_Contact, @p_ContactTitle, @p_Tel, @p_Mobile, @p_Fax, @p_Address, @p_ZipCode, @p_BankAccount, @p_Bank, @p_Credit, @p_CreditPeriod, @p_TaxAccount, @p_Region, @p_VendorType, @p_Remark, @p_RecordId out, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
					new SqlParameter("@p_VendorNo", SqlDbType.VarChar, 8),
					new SqlParameter("@p_VendorName", SqlDbType.VarChar, 200),
					new SqlParameter("@p_ShortName", SqlDbType.VarChar, 60),
					new SqlParameter("@p_Contact", SqlDbType.VarChar, 20),
					new SqlParameter("@p_ContactTitle", SqlDbType.VarChar, 20),
					new SqlParameter("@p_Tel", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Mobile", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Fax", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Address", SqlDbType.VarChar, 200),
					new SqlParameter("@p_ZipCode", SqlDbType.VarChar, 6),
					new SqlParameter("@p_BankAccount", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Bank", SqlDbType.VarChar, 60),
					new SqlParameter("@p_Credit", SqlDbType.Decimal, 15),
					new SqlParameter("@p_CreditPeriod", SqlDbType.Int),
                    new SqlParameter("@p_TaxAccount", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Region", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_VendorType", SqlDbType.Int),
                    new SqlParameter("@p_Remark", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = vendor.VendorNo.Trim();
            parms[2].Value = vendor.VendorName.Trim();
            parms[3].Value = vendor.ShortName.Trim();
            parms[4].Value = vendor.Contact.Trim();
            parms[5].Value = vendor.ContactTitle.Trim();
            parms[6].Value = vendor.Tel.Trim();
            parms[7].Value = vendor.Mobile.Trim();
            parms[8].Value = vendor.Fax.Trim();
            parms[9].Value = vendor.Address.Trim();
            parms[10].Value = vendor.ZipCode.Trim();
            parms[11].Value = vendor.BankAccount.Trim();
            parms[12].Value = vendor.Bank.Trim();
            parms[13].Value = vendor.Credit;
            parms[14].Value = vendor.CreditPeriod;
            parms[15].Value = vendor.TaxAccount.Trim();
            parms[16].Value = vendor.Region.Trim();
            parms[17].Value = vendor.VendorType;
            parms[18].Value = vendor.Remark.Trim();
            parms[19].Value = vendor.RecordId;
            parms[19].Direction = ParameterDirection.InputOutput;
            parms[20].Value = "";
            parms[20].Direction = ParameterDirection.InputOutput;
            parms[21].Value = 0;
            parms[21].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[19].Value;
        }
    }
}
